/**
 * 
 */
package com.ericsson.bscs.cmstest.clients ;

import java.io.InputStream ;
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
import java.sql.SQLException ;
import java.sql.Statement ;
import java.util.ArrayList ;
import java.util.Iterator ;
import java.util.List ;
import java.util.Properties ;
import java.util.logging.Logger ;

import javax.naming.NamingException ;

import oracle.jdbc.driver.OracleDriver ;

/**
 * @author Igor Kostin: igor.kstn@gmail.com
 * 
 */
public class SOILoaderDBHandler
{
	private static Logger _log = Logger.getLogger(SOILoaderDBHandler.class.getCanonicalName()) ;

	private Connection bscsConn = null ;
	private Statement batchStmt = null ;
	private static String defPropertyFile = "database.properties" ;
	public static final String PROP_DBUSER = "user" ;
	public static final String PROP_DBPASS = "password" ;
	public static final String PROP_DBURL = "url" ;
	private String userName ;
	private String passwd ;
	private String url ;

	private final static String SQL_customerIds = "SELECT DISTINCT CUSTOMER_ID FROM CCONTACT_ALL WHERE CCLNAME LIKE 'LN-%' ORDER BY CUSTOMER_ID DESC" ;
	private final static String SQL_COL_customerId = "CUSTOMER_ID" ;

	private List<Long> customerIds ;
	private volatile Iterator<Long> indexCustomerId ;

	public SOILoaderDBHandler() throws NamingException, SQLException
	{
		initialize() ;
		getDBConnection() ;
	}

	public void closeDBConnection()
	{
		try
		{
			if (batchStmt != null)
			{
				batchStmt.clearBatch() ;
				batchStmt = null ;
			}
			if (bscsConn != null)
			{
				bscsConn.close() ;
				bscsConn = null ;
			}
		}
		catch (final SQLException e)
		{
			bscsConn = null ;
			e.printStackTrace() ;
		}
	}

	private ResultSet executeQuery(final String SQL) throws NamingException, SQLException
	{
		_log.finest("Executing: " + SQL) ;
		ResultSet rs = null ;
		if (bscsConn == null)
			bscsConn = openDB() ;
		final PreparedStatement ps = bscsConn.prepareStatement(SQL) ;
		rs = ps.executeQuery() ;
		return rs ;
	}

	private void executeStatement(final String SQL) throws SQLException, NamingException
	{
		_log.finest("Executing: " + SQL) ;
		if (bscsConn == null || bscsConn.isClosed())
			bscsConn = openDB() ;
		final PreparedStatement ps = bscsConn.prepareStatement(SQL) ;
		ps.execute() ;
		bscsConn.commit() ;
	}

	private void fillCustomerIds() throws SQLException, NamingException
	{
		final ResultSet rs = executeQuery(SQL_customerIds) ;
		if(customerIds == null)
			customerIds = new ArrayList<Long>() ;
		else
			customerIds.clear();
		while (rs.next())
		{
			final long customerId = rs.getLong(SQL_COL_customerId) ;
			customerIds.add(customerId) ;
		}
		rs.close() ;
		indexCustomerId = customerIds.iterator() ;
	}

	private Connection getDBConnection() throws NamingException, SQLException
	{
		if (bscsConn == null)
			bscsConn = openDB() ;
		return bscsConn ;
	}

	public List<CustContractEntry> getListOfCustomerContractId() throws SQLException, NamingException
	{
		final List<CustContractEntry> records = new ArrayList<CustContractEntry>() ;
		final String SQL = "SELECT CUSTOMER_ID, CO_ID FROM cust_co_udr_lt" ;

		final ResultSet rs = executeQuery(SQL) ;
		while (rs.next())
		{
			final CustContractEntry cce = new CustContractEntry() ;
			cce.setCUSTOMER_ID(new Integer(rs.getInt("CUSTOMER_ID"))) ;
			cce.setCO_ID(new Integer(rs.getInt("CO_ID"))) ;
			records.add(cce) ;
		}
		rs.close() ;
		return records ;
	}

	private void initialize()
	{
		final Properties dbProps = new Properties() ;
		try
		{
			// read the database.properties file
			final InputStream resourceAsStream = ClassLoader.getSystemResourceAsStream(defPropertyFile) ;
			dbProps.load(resourceAsStream) ;

			// FileInputStream fstream = new FileInputStream(defPropertyFile);
			// dbProps.load(fstream);

			userName = dbProps.getProperty("user") ;
			passwd = dbProps.getProperty("password") ;
			url = dbProps.getProperty("url") ;
		}
		catch (final Exception ex)
		{
			_log.info("Failed to load " + defPropertyFile + " file.") ;
			ex.printStackTrace() ;
		}
	}

	synchronized public long nextCustomerId() throws SQLException, NamingException
	{
		if (customerIds == null || !indexCustomerId.hasNext())
			fillCustomerIds() ;
		return indexCustomerId.next() ;
	}

	private Connection openDB() throws SQLException
	{
		if (bscsConn == null)
		{
			// String urlStr = "jdbc:oracle:thin:" + this.userName + "/" + this.passwd + "@" + this.serverName + ":" + this.portNum + ":" + this.databaseName;
			DriverManager.registerDriver(new OracleDriver()) ;
			bscsConn = DriverManager.getConnection(url, userName, passwd) ;
			_log.info("Database connection established: " + url) ;
		} else
			_log.info("Database connection already exists, not reconnecting") ;
		return bscsConn ;
	}
}
